<?php

namespace app\admin\controller;

use app\common\controller\AdminBase;
use think\App;
use think\exception\ValidateException;
use util\Excel;
use util\Upload;
use app\dic\DicOrder;
use think\cache\driver\Redis;
use util\Token;

/**
 * Qustiontype控制器
 */
class Customerjourney extends AdminBase
{

    protected $model = null;

    protected $validate = null;

    protected $relationSearch = false;
    protected $total_price_percentage = 0;
    protected $total_price_sum = 0;
    protected $count = 0;
    protected $total_price = 0;
    protected $freight = 0;
    protected $material = 0;
    protected $GrossProfit = 0;
    protected $SingleQuantity = 0;
    protected $count_sku = 0;
    protected $arr_sku = [];

    public function __construct(App $app)
    {
        parent::__construct($app);

        $this->total_price_percentage = 0;
        $this->total_price_sum = 0;
        $this->count = 0;
        $this->total_price = 0;
        $this->freight = 0;
        $this->material = 0;
        $this->GrossProfit = 0;
        $this->SingleQuantity = 0;
        $this->count_sku = 0;
        $this->arr_sku = [];
        $this->model = new \app\admin\model\Customerjourneysummary();
        $this->validate = \app\admin\validate\Customerjourneysummary::class;
    }

    /**
     * 列表
     */
    public function index()
    {
        list($limit, $where, $sortArr) = $this->buildTableParames();
        $filters = $this->request->get('filter', '{}');
        $filters = json_decode($filters, true);
        $where2 = array();
        if (isset($filters['name'])) {
            $order_ids = trim($filters['name']);
            $order_ids = explode("\n", $order_ids);
            $order_ids_new = array();
            if (!empty($order_ids)) {
                foreach ($order_ids as $k => $vids) {
                    if (!empty($vids)) {
                        $vids = trim($vids);
                        $order_ids_new[] = $vids;
                    }
                }
            }
            if (!empty($order_ids_new)) {
                $where2[] = ['a.id|a.name|a.order_number', 'in', $order_ids_new];
            }
        }
        if (!empty($filters['number'])) {
            $where2[] = array('a.order_number', 'LIKE', '%' . trim($filters['number']) . '%');
        }
        if (!empty($filters['skunum'])) {
            $where2[] = array('a.two_kf_id', '=',trim($filters['skunum']));
        }
        if (!empty($filters['tag_campaign'])) {
            if($filters['tag_campaign'] == "null"){
                $where2[] = array('b.tag_campaign', '=', '');
            }else{
                $where2[] = array('b.tag_campaign', 'LIKE', '%' . trim($filters['tag_campaign']) . '%');
            }
        }
        if (!empty($filters['tag_source'])) {
            if($filters['tag_source'] == "null"){
                $where2[] = array('b.tag_source', '=', '');
            }else{
                $where2[] = array('b.tag_source', 'LIKE', '%' . trim($filters['tag_source']) . '%');
            }
        }
        if (!empty($filters['created_at'])) {
            $where2[]  = ['a.created_at', '>=', strtotime($filters['created_at'][0])];
            $where2[]  = ['a.created_at', '<=', strtotime($filters['created_at'][1]) + 3600*24-1];
        } 
        if (!empty($filters['status'])) {
            if($filters['status'] == "matched"){
                $where2[] = array('b.status', '=',1);
            }
            if($filters['status'] == "manually_modifying"){
                $where2[] = array('b.status', '=',3);
            }
            if($filters['status'] == "unmatched"){
                $where2[] = array('b.status', '=',0);
                // $where2[] = array('b.status', "=",null);
                // $where2[]  = ['a.created_at', '>=', 1709222400];
            }
            if($filters['status'] == "matching_failed"){
                $where2[] = array('b.status', '=',2);
            }
            if($filters['status'] == "fb"){
                $where2[] = array('b.tag_source', 'LIKE', '%fb%');
            }
            if($filters['status'] == "tt"){
                $where2[] = array('b.tag_source', 'LIKE', '%tt%');
            }
            if($filters['status'] == "ggad"){
                $where2[] = array('b.tag_source', 'LIKE', '%ggad%');
            }
            if($filters['status'] == "email"){
                $where2[] = array('b.tag_source', 'LIKE', '%email%');
            }
            if($filters['status'] == "other"){
                $where2[] = array('b.tag_source', 'LIKE', '%other%');
            }
            if($filters['status'] == "direct_access"){
                $where2[] = array('b.tag_source', 'LIKE', '%直接访问%');
            }
            if($filters['status'] == "AllianceInternetCelebrity"){
                $where2[] = array('b.tag_source', 'LIKE', '%impact%');
            }
        }
        $where3 = [];
        if (!empty($filters['key_words'])) {
            $keyword = '%' . $filters['key_words'] . '%';
            $where3[] = ['a.landing_site|c.lastVisit_campaign|c.lastVisit_source|c.firstVisit_campaign|c.firstVisit_source|b.tag_source|b.tag_campaign|b.first_source|b.first_campaign|b.last_source|b.last_campaign', 'like', $keyword];
        }
        if(!empty($where3) || !empty($where2)){
            $start_at = date('Y-m-d 00:00:00', time()-16*3600-365*24*3600);
            $end_at = date('Y-m-d 23:59:59', time()-16*3600);

            // $where2[]  = ['a.created_at', '>=', strtotime($start_at)];
            // $where2[]  = ['a.created_at', '<=', strtotime($end_at)];
        }else{
            if (isset($filters['day_changes'])) {
                switch ($filters['day_changes']) {
                    case '0':
                        $start_at = date('Y-m-d 00:00:00', time()-16*3600);
                        $end_at = date('Y-m-d 23:59:59', time()-16*3600);
                        break;
                    case '1':
                        $start_at = date('Y-m-d 00:00:00', time()-16*3600-24*3600);
                        $end_at = date('Y-m-d 23:59:59', time()-16*3600-24*3600);
                        break;
                    case '2':
                        $start_at = date('Y-m-d 00:00:00', time()-16*3600-7*24*3600);
                        $end_at = date('Y-m-d 23:59:59', time()-16*3600);
                        break;
                    case '3':
                        $start_at = date('Y-m-d 00:00:00', time()-16*3600-365*24*3600);
                        $end_at = date('Y-m-d 23:59:59', time()-16*3600);
                        break;
                    default:
                }
                $where2[]  = ['a.created_at', '>=', strtotime($start_at)];
                $where2[]  = ['a.created_at', '<=', strtotime($end_at)];
            }
        }
        
        if (!empty($filters['tag'])) {
            $where4 = [];
            $keywordtag = '%' . $filters['tag'] . '%';
            $where4[] = ['d.sku', 'like', $keywordtag];

            // 2024-05-25 支持多sku
            // $keywordtag = explode(',',$filters['tag']);
            // $where4[] = ['d.sku', 'in', $keywordtag];

            
            $where_total_price[] = ['a.financial_status', 'in', [DicOrder::$financial_status_partially_paid, DicOrder::$financial_status_paid, DicOrder::$financial_status_partially_refunded, DicOrder::$financial_status_refunded]];
            $list = table('order')
                ->field('a.id,a.name,a.order_number,a.created_at,a.shipping_name,a.total_price,a.financial_status,a.fulfillment_status,b.first_source,b.first_campaign,b.last_source,b.last_campaign,b.status,b.tag_source,b.tag_campaign,
                c.firstVisit_source,c.firstVisit_campaign,c.lastVisit_source,c.lastVisit_campaign,c.firstVisit_landingPage,a.landing_site,d.sku,a.tags')
                ->alias('a')
                ->join('qu_customerjourney b','b.order_number = a.order_number', 'left')
                ->join('qu_customerjourneysummary c','c.order_id = a.id', 'left')
                ->join('qu_order_quantity d','d.order_id = a.id', 'left')
                ->where('a.site_id','121')
                ->where($where_total_price)
                ->where($where2)
                ->where($where3)
                ->where($where4)
                ->order('a.created_at desc')
                ->group('a.id')
                ->paginate($limit);
        }else{
            $where_total_price[] = ['a.financial_status', 'in', [DicOrder::$financial_status_partially_paid, DicOrder::$financial_status_paid, DicOrder::$financial_status_partially_refunded, DicOrder::$financial_status_refunded]];
            $list = table('order')
                ->field('a.id,a.name,a.order_number,a.created_at,a.shipping_name,a.total_price,a.financial_status,a.fulfillment_status,b.first_source,b.first_campaign,b.last_source,b.last_campaign,b.status,b.tag_source,b.tag_campaign,
                c.firstVisit_source,c.firstVisit_campaign,c.lastVisit_source,c.lastVisit_campaign,c.firstVisit_landingPage,a.landing_site,a.tags')
                ->alias('a')
                ->join('qu_customerjourney b','b.order_number = a.order_number', 'left')
                ->join('qu_customerjourneysummary c','c.order_id = a.id', 'left')
                ->where('a.site_id','121')
                ->where($where_total_price)
                ->where($where2)
                ->where($where3)
                ->order('a.created_at desc')
                ->group('a.id')
                ->paginate($limit);
        }
        $lists = $list->items();
        // halt($lists);
        foreach ($lists as $k=>$v) {
            // 状态 默认0 成功1失败2
            switch ($v['status']) {
                case '0':
                    $lists[$k]['status_type'] = '等待匹配';
                    break;
                case '1':
                    $lists[$k]['status_type'] = '已匹配';
                    break;
                case '2':
                    $lists[$k]['status_type'] = '匹配失败';
                    break;
                case '3':
                    $lists[$k]['status_type'] = '手动修改标签';
                    break;
                default:
                    $lists[$k]['status_type'] = '未找到';
            }
            $lists[$k]['created_at'] = date('Y-m-d H:i', $v['created_at']);
            //SKU详情
            $redis = new Redis();
            $orderinfo = $redis->get('orderinfo'.$v['id']);
            if(empty($orderinfo)){
                $orderinfo = table('order_quantity')
                    ->field('a.product_id,b.tags,a.sku')
                    ->alias('a')
                    ->join('qu_product b','b.id = a.product_id', 'left')
                    ->where('a.order_id',$v['id'])
                    ->select()
                    ->toarray();
                    if(!empty($orderinfo)){
                        foreach ($orderinfo as $kinfo=>$vinfo) {
                            if(!empty($vinfo['sku'])){
                                $pos = strpos($vinfo['sku'], '-');
                                if ($pos !== false) {
                                    $sku = explode('-', $vinfo['sku']);
                                    $orderinfo[$kinfo]['brand'] = $sku[0];
                                }
                            }
                        }
                    }else{
                        $orderinfo[0]['brand'] = "";
                        $orderinfo[0]['tags'] = "";
                    }
                    $redis->set('orderinfo'.$v['id'], $orderinfo, 3600);
            }
            $lists[$k]['skus_info'] = $orderinfo;
            $lists[$k]['firstVisit_source'] = !empty($v['firstVisit_source']) ? $v['firstVisit_source'] : "API暂无数据";
            $lists[$k]['firstVisit_campaign'] = !empty($v['firstVisit_campaign']) ? $v['firstVisit_campaign'] : "API暂无数据";
            $lists[$k]['lastVisit_source'] = !empty($v['lastVisit_source']) ? $v['lastVisit_source'] : "API暂无数据";
            $lists[$k]['lastVisit_campaign'] = !empty($v['lastVisit_campaign']) ? $v['lastVisit_campaign'] : "API暂无数据";
        }
        $table_customerjourney = table('customerj_today')->where('status',0)->order('time desc')->select();
        //下拉列表
        $Taskcustomerjourney = new \app\admin\controller\Taskcustomerjourney($this->app);
        $config = $Taskcustomerjourney->config();

        $tag_source_arr = [];
        foreach (array_unique($config['str8']) as $k=>$v) {
            $tag_source_arr[] = [
                'label'   => $v,
                'value' => $v,
            ];
        }
        $tag_campaign_arr = [];
        foreach (array_unique($config['str9']) as $k=>$v) {
            $tag_campaign_arr[] = [
                'label'   => $v,
                'value' => $v,
            ];
        }

        $data = [
            'code' => 1,
            'msg' => '',
            'count' => $list->total(),
            'data' => $lists,
            'table_customerjourney' => $table_customerjourney,
            'tag_source' => $tag_source_arr,
            'tag_campaign' => $tag_campaign_arr,
        ];
        return json($data);
    }

    public function add()
    {
        $post = $this->request->post();
            $result = $this->model->save($post);
            if ($result) {
                return success('添加成功！');
            }
            return error('添加失败');
    }

    //批量修改
    public function batch_modification()
    {
        $Taskcustomerjourney = new \app\admin\controller\Taskcustomerjourney($this->app);
        $str = $Taskcustomerjourney->config();

        $post = $this->request->post();
        $data = $post['paramsArray'];
        
        $num_error = 0;
        $num_success = 0;
        if(!empty($data)){
            foreach ($data as $key=>$value) {
                if(isset($value["tag_campaign"]) && $value["tag_campaign"] == "空值"){
                    $value["tag_campaign"] = "";
                }else{
                    if(!empty($value["tag_campaign"]) && empty($value["tag_source"])){
                        $value["tag_source"] = $Taskcustomerjourney->tag_campaign_tag($value["tag_campaign"],$str);
                    }
                }
                if(isset($value["tag_source"]) && $value["tag_source"] == "空值"){
                    $value["tag_source"] = "";
                }
                $arr_insert = [
                    'tag_source' => isset($value["tag_source"]) ? strtolower($value["tag_source"]) : "",
                    'tag_campaign' => isset($value["tag_campaign"]) ? strtolower($value["tag_campaign"]) : "",
                    'status' => 3,
                ];
                
                // $msg_data = array();
                // if(!empty($value[1])){
                //     $msg_data[] = strtolower($value["tag_source"]);
                // }
                // if(!empty($value[2])){
                //     $msg_data[] = strtolower($value["tag_campaign"]);
                // }
                // if(!empty($msg_data)){
                //     $tags = implode(',',$msg_data);
                // }else{
                //     $tags = "";
                // }
                // $arr_insert2 = [
                //     'number' => $value['order_number'],
                //     'tags' => $tags,
                //     'type' => 0,
                // ];
                // $customerj_handle = table('customerj_handle')->where('number',$arr_insert2['number'])->find();
                // if(!empty($customerj_handle)){
                    // table('customerj_handle')->where('number',$arr_insert2['number'])->update($arr_insert2);
                // }else{
                    // table('customerj_handle')->insert($arr_insert2);
                // }
                $customerjourney = table('customerjourney')
                    ->where('order_number', $value['order_number'])
                    ->update($arr_insert);
                // if (!empty($customerjourney)) {
                    $num_success = $num_success + 1;
                // }else{
                    // $num_error = $num_error + 1;
                // }
            }
        }
        $info = "成功" . $num_success . "个";
        // if($num_success == 0){
        //     return error("导入完成：".$info);
        // }else{
            return success("修改完成：".$info);
        // }
    }

    /**
     * 导出
     */
    public function export()
    {
        $tableName = $this->model->getName();

        list($limit, $where, $sortArr) = $this->buildTableParames();
        $filters = $this->request->get('filter', '{}');
        $filters = json_decode($filters, true);
        $where2 = array();
        if (isset($filters['name'])) {
            $order_ids = trim($filters['name']);
            $order_ids = explode("\n", $order_ids);
            $order_ids_new = array();
            if (!empty($order_ids)) {
                foreach ($order_ids as $k => $vids) {
                    if (!empty($vids)) {
                        $vids = trim($vids);
                        $order_ids_new[] = $vids;
                    }
                }
            }
            if (!empty($order_ids_new)) {
                $where2[] = ['a.id|a.name|a.order_number', 'in', $order_ids_new];
            }
        }
        if (!empty($filters['number'])) {
            $where2[] = array('a.order_number', 'LIKE', '%' . trim($filters['number']) . '%');
        }
        if (!empty($filters['skunum'])) {
            $where2[] = array('a.two_kf_id', '=',trim($filters['skunum']));
        }
        if (!empty($filters['tag_campaign'])) {
            if($filters['tag_campaign'] == "null"){
                $where2[] = array('b.tag_campaign', '=', '');
            }else{
                $where2[] = array('b.tag_campaign', 'LIKE', '%' . trim($filters['tag_campaign']) . '%');
            }
        }
        if (!empty($filters['tag_source'])) {
            if($filters['tag_source'] == "null"){
                $where2[] = array('b.tag_source', '=', '');
            }else{
                $where2[] = array('b.tag_source', 'LIKE', '%' . trim($filters['tag_source']) . '%');
            }
        }
        if (!empty($filters['created_at'])) {
            $where2[]  = ['a.created_at', '>=', strtotime($filters['created_at'][0])];
            $where2[]  = ['a.created_at', '<=', strtotime($filters['created_at'][1]) + 3600*24-1];
        } 
        if (!empty($filters['status'])) {
            if($filters['status'] == "matched"){
                $where2[] = array('b.status', '=',1);
            }
            if($filters['status'] == "manually_modifying"){
                $where2[] = array('b.status', '=',3);
            }
            if($filters['status'] == "unmatched"){
                $where2[] = array('b.status', '=',0);
                // $where2[] = array('b.status', "=",null);
                // $where2[]  = ['a.created_at', '>=', 1709222400];
            }
            if($filters['status'] == "matching_failed"){
                $where2[] = array('b.status', '=',2);
            }
            if($filters['status'] == "fb"){
                $where2[] = array('b.tag_source', 'LIKE', '%fb%');
            }
            if($filters['status'] == "tt"){
                $where2[] = array('b.tag_source', 'LIKE', '%tt%');
            }
            if($filters['status'] == "ggad"){
                $where2[] = array('b.tag_source', 'LIKE', '%ggad%');
            }
            if($filters['status'] == "email"){
                $where2[] = array('b.tag_source', 'LIKE', '%email%');
            }
            if($filters['status'] == "other"){
                $where2[] = array('b.tag_source', 'LIKE', '%other%');
            }
            if($filters['status'] == "direct_access"){
                $where2[] = array('b.tag_source', 'LIKE', '%直接访问%');
            }
            if($filters['status'] == "AllianceInternetCelebrity"){
                $where2[] = array('b.tag_source', 'LIKE', '%impact%');
            }
        }
        $where3 = [];
        if (!empty($filters['key_words'])) {
            $keyword = '%' . $filters['key_words'] . '%';
            $where3[] = ['a.landing_site|c.lastVisit_campaign|c.lastVisit_source|c.firstVisit_campaign|c.firstVisit_source|b.tag_source|b.tag_campaign|b.first_source|b.first_campaign|b.last_source|b.last_campaign', 'like', $keyword];
        }
        if(!empty($where3) || !empty($where2)){
            $start_at = date('Y-m-d 00:00:00', time()-16*3600-365*24*3600);
            $end_at = date('Y-m-d 23:59:59', time()-16*3600);

            // $where2[]  = ['a.created_at', '>=', strtotime($start_at)];
            // $where2[]  = ['a.created_at', '<=', strtotime($end_at)];
        }else{
            if (isset($filters['day_changes'])) {
                switch ($filters['day_changes']) {
                    case '0':
                        $start_at = date('Y-m-d 00:00:00', time()-16*3600);
                        $end_at = date('Y-m-d 23:59:59', time()-16*3600);
                        break;
                    case '1':
                        $start_at = date('Y-m-d 00:00:00', time()-16*3600-24*3600);
                        $end_at = date('Y-m-d 23:59:59', time()-16*3600-24*3600);
                        break;
                    case '2':
                        $start_at = date('Y-m-d 00:00:00', time()-16*3600-7*24*3600);
                        $end_at = date('Y-m-d 23:59:59', time()-16*3600);
                        break;
                    case '3':
                        $start_at = date('Y-m-d 00:00:00', time()-16*3600-365*24*3600);
                        $end_at = date('Y-m-d 23:59:59', time()-16*3600);
                        break;
                    default:
                }
                $where2[]  = ['a.created_at', '>=', strtotime($start_at)];
                $where2[]  = ['a.created_at', '<=', strtotime($end_at)];
            }
        }
        
        if (!empty($filters['tag'])) {
            $where4 = [];
            $keywordtag = '%' . $filters['tag'] . '%';
            $where4[] = ['d.sku', 'like', $keywordtag];

            // 2024-05-25 支持多sku
            // $keywordtag = explode(',',$filters['tag']);
            // $where4[] = ['d.sku', 'in', $keywordtag];

            
            $where_total_price[] = ['a.financial_status', 'in', [DicOrder::$financial_status_partially_paid, DicOrder::$financial_status_paid, DicOrder::$financial_status_partially_refunded, DicOrder::$financial_status_refunded]];
            $list = table('order')
                ->field('a.id,a.name,a.order_number,a.created_at,a.shipping_name,a.total_price,a.financial_status,a.fulfillment_status,b.first_source,b.first_campaign,b.last_source,b.last_campaign,b.status,b.tag_source,b.tag_campaign,
                c.firstVisit_source,c.firstVisit_campaign,c.lastVisit_source,c.lastVisit_campaign,c.firstVisit_landingPage,a.landing_site,d.sku,a.tags')
                ->alias('a')
                ->join('qu_customerjourney b','b.order_number = a.order_number', 'left')
                ->join('qu_customerjourneysummary c','c.order_id = a.id', 'left')
                ->join('qu_order_quantity d','d.order_id = a.id', 'left')
                ->where('a.site_id','121')
                ->where($where_total_price)
                ->where($where2)
                ->where($where3)
                ->where($where4)
                ->order('a.created_at desc')
                ->group('a.id')
                ->select()
                ->toArray();
        }else{
            $where_total_price[] = ['a.financial_status', 'in', [DicOrder::$financial_status_partially_paid, DicOrder::$financial_status_paid, DicOrder::$financial_status_partially_refunded, DicOrder::$financial_status_refunded]];
            $list = table('order')
                ->field('a.id,a.name,a.order_number,a.created_at,a.shipping_name,a.total_price,a.financial_status,a.fulfillment_status,b.first_source,b.first_campaign,b.last_source,b.last_campaign,b.status,b.tag_source,b.tag_campaign,
                c.firstVisit_source,c.firstVisit_campaign,c.lastVisit_source,c.lastVisit_campaign,c.firstVisit_landingPage,a.landing_site,a.tags')
                ->alias('a')
                ->join('qu_customerjourney b','b.order_number = a.order_number', 'left')
                ->join('qu_customerjourneysummary c','c.order_id = a.id', 'left')
                ->where('a.site_id','121')
                ->where($where_total_price)
                ->where($where2)
                ->where($where3)
                ->order('a.created_at desc')
                ->group('a.id')
                ->select()
                ->toArray();
        }

        $header = array();
        $header[] = ['订单号', 'name'];
        $header[] = ['订单编号', 'order_number'];
        $header[] = ['订单状态', 'financial_status'];
        $header[] = ['运营标签', 'tag_campaign'];
        $header[] = ['渠道', 'tag_source'];
        $header[] = ['last_campaign', 'last_campaign'];
        $header[] = ['last_source', 'last_source'];
        $header[] = ['first_campaign', 'first_campaign'];
        $header[] = ['first_source', 'first_source'];
        $header[] = ['landing_site', 'landing_site'];
        $header[] = ['原标签参考', 'tags'];

        $fileName = "export_" . $tableName . "_" . time();
        return Excel::exportData($list, $header, $fileName, 'xlsx');
    }
    /**
     * 导出
     */
    public function export_achievement()
    {
        $tableName = $this->model->getName();

        list($limit, $where, $sortArr) = $this->buildTableParames();
        $filters = $this->request->get('filter', '{}');
        $filters = json_decode($filters, true);
        $lists_news = $this->lists_news($filters);
        
        $header = array();
        $header[] = ['渠道', 'tag_source'];
        $header[] = ['运营', 'tag_campaign'];
        $header[] = ['销售额', 'total_price'];
        $header[] = ['广告成本', 'advcost'];
        $header[] = ['ROI', 'roi'];
        $header[] = ['币种', 'currency'];
        $header[] = ['单量', 'count'];

        $fileName = "performance_allocation" . time();
        return Excel::exportData($lists_news, $header, $fileName, 'xlsx');
    }

    //测试
    public function sales_performance_list()
    {
        list($limit, $where, $sortArr) = $this->buildTableParames();
        $filters = $this->request->get('filter', '{}');
        $filters = json_decode($filters, true);
        $lists_news = $this->lists_news($filters);

        $lists_news_total = array(
            'total_price' => "总销售额".$this->total_price_sum,
            'currency' => "",
            'count' => "总单量".$this->count,
            'roi' => "",
            'tag_source' => "",
            'advcost' => "",
            'tag_campaign' => "统计"
        );
        $data = [
            'code' => 1,
            'msg' => '',
            'data' => $lists_news,
            'lists_news_total' => $lists_news_total,
        ];
        return json($data);

    }

    //测试
    public function sales_performance_skulist()
    {
        list($limit, $where, $sortArr) = $this->buildTableParames();
        $filters = $this->request->get('filter', '{}');
        $filters = json_decode($filters, true);
        $lists_news = $this->lists_news_sku($filters);

        $lists_news_total = array(
            'total_price' => "销售额$:".$this->total_price,
            'freight' => "物流成本$:".$this->freight,
            'material' => "采购成本$:".$this->material,
            'GrossProfit' => "件数:".$this->SingleQuantity."--单量:".$this->count_sku,
            'tag_campaign' => "统计"
        );

        $redis = new Redis();
        // 查询用户id
        $token = $this->request->header('token');
        $adminId = Token::userId($token);

        $redis->set('lists_news_sku'.$adminId, $lists_news, 60);
        $redis->set('lists_news_total'.$adminId, $lists_news_total, 60);

        $data = [
            'code' => 1,
            'msg' => '',
            'data' => $lists_news,
            'lists_news_total' => $lists_news_total,
        ];
        return json($data);

    }

    /**
     * 导出SKU明细
     */
    public function export_achievement_sku()
    {
        $tableName = $this->model->getName();

        list($limit, $where, $sortArr) = $this->buildTableParames();
        $filters = $this->request->get('filter', '{}');
        $filters = json_decode($filters, true);

        $redis = new Redis();
        // 查询用户id
        $token = $this->request->header('token');
        $adminId = Token::userId($token);

        $lists_news = $redis->get('lists_news_sku'.$adminId);
        $lists_news_total = $redis->get('lists_news_total'.$adminId);
        if(empty($lists_news) || empty($lists_news_total)){
            $lists_news = $this->lists_news_sku($filters);
            $lists_news_total = array(
                'total_price' => "销售额$:".$this->total_price,
                'tag_campaign' => "物流成本$:".$this->freight,
                'name' => "采购成本$:".$this->material,
                'quantity' => "件数:".$this->SingleQuantity."--单量:".$this->count_sku,
            );
        }else{
            $lists_news_total['quantity'] = $lists_news_total['GrossProfit'];
            $lists_news_total['tag_campaign'] = $lists_news_total['freight'];
            $lists_news_total['name'] = $lists_news_total['material'];
        }
        $lists_news[] = $lists_news_total;
        $header = array();
        $header[] = ['SKU', 'tag_campaign'];
        $header[] = ['运营', 'name'];
        $header[] = ['件数', 'quantity'];
        $header[] = ['销售额', 'total_price'];

        $fileName = "performance_SKU" . time();
        return Excel::exportData($lists_news, $header, $fileName, 'xlsx');
    }


    /**
    * 方法详情
    * @param array params
    */
    public function lists_news($filters)
    {
        $where2 = array();
        if (isset($filters['name'])) {
            $order_ids = trim($filters['name']);
            $order_ids = explode("\n", $order_ids);
            $order_ids_new = array();
            if (!empty($order_ids)) {
                foreach ($order_ids as $k => $vids) {
                    if (!empty($vids)) {
                        $vids = trim($vids);
                        $order_ids_new[] = $vids;
                    }
                }
            }
            if (!empty($order_ids_new)) {
                $where2[] = ['a.id|a.name|a.order_number', 'in', $order_ids_new];
            }
        }
        if (!empty($filters['number'])) {
            $where2[] = array('a.order_number', 'LIKE', '%' . trim($filters['number']) . '%');
        }
        if (!empty($filters['skunum'])) {
            $where2[] = array('a.two_kf_id', '=',trim($filters['skunum']));
        }
        if (!empty($filters['tag_campaign'])) {
            if($filters['tag_campaign'] == "null"){
                $where2[] = array('b.tag_campaign', '=', '');
            }else{
                $where2[] = array('b.tag_campaign', 'LIKE', '%' . trim($filters['tag_campaign']) . '%');
            }
        }
        if (!empty($filters['tag_source'])) {
            if($filters['tag_source'] == "null"){
                $where2[] = array('b.tag_source', '=', '');
            }else{
                $where2[] = array('b.tag_source', 'LIKE', '%' . trim($filters['tag_source']) . '%');
            }
        }  
        $adv_where = [];
        if (!empty($filters['created_at'])) {
            $where2[]  = ['a.created_at', '>=', strtotime($filters['created_at'][0])];
            $where2[]  = ['a.created_at', '<=', strtotime($filters['created_at'][1]) + 3600*24-1];
        }
        if (!empty($filters['status'])) {
            if($filters['status'] == "matched"){
                $where2[] = array('b.status', '=',1);
            }
            if($filters['status'] == "manually_modifying"){
                $where2[] = array('b.status', '=',3);
            }
            if($filters['status'] == "unmatched"){
                $where2[] = array('b.status', "=",null);
                $where2[]  = ['a.created_at', '>=', 1709222400];
            }
            if($filters['status'] == "matching_failed"){
                $where2[] = array('b.status', '=',2);
            }
            if($filters['status'] == "fb"){
                $where2[] = array('b.tag_source', 'LIKE', '%fb%');
            }
            if($filters['status'] == "tt"){
                $where2[] = array('b.tag_source', 'LIKE', '%tt%');
            }
            if($filters['status'] == "ggad"){
                $where2[] = array('b.tag_source', 'LIKE', '%ggad%');
            }
            if($filters['status'] == "email"){
                $where2[] = array('b.tag_source', 'LIKE', '%email%');
            }
            if($filters['status'] == "other"){
                $where2[] = array('b.tag_source', 'LIKE', '%other%');
            }
            if($filters['status'] == "direct_access"){
                $where2[] = array('b.tag_source', 'LIKE', '%直接访问%');
            }
        }
        $where3 = [];
        if (!empty($filters['key_words'])) {
            $keyword = '%' . $filters['key_words'] . '%';
            $where3[] = ['a.landing_site|c.lastVisit_campaign|c.lastVisit_source|c.firstVisit_campaign|c.firstVisit_source|b.tag_source|b.tag_campaign|b.first_source|b.first_campaign|b.last_source|b.last_campaign', 'like', $keyword];
        }
        if(!empty($where3) || !empty($where2)){
            $start_at = date('Y-m-d 00:00:00', time()-16*3600-365*24*3600);
            $end_at = date('Y-m-d 23:59:59', time()-16*3600);

            // $where2[]  = ['a.created_at', '>=', strtotime($start_at)];
            // $where2[]  = ['a.created_at', '<=', strtotime($end_at)];

            // 广告以及ROI
            $adv_where[]  = ['create_time', '>=', $start_at];
            $adv_where[]  = ['create_time', '<', $end_at];
        }else{
            if (isset($filters['day_changes'])) {
                switch ($filters['day_changes']) {
                    case '0':
                        $start_at = date('Y-m-d 00:00:00', time()-16*3600);
                        $end_at = date('Y-m-d 23:59:59', time()-16*3600);
                        break;
                    case '1':
                        $start_at = date('Y-m-d 00:00:00', time()-16*3600-24*3600);
                        $end_at = date('Y-m-d 23:59:59', time()-16*3600-24*3600);
                        break;
                    case '2':
                        $start_at = date('Y-m-d 00:00:00', time()-16*3600-7*24*3600);
                        $end_at = date('Y-m-d 23:59:59', time()-16*3600);
                        break;
                    case '3':
                        $start_at = date('Y-m-d 00:00:00', time()-16*3600-365*24*3600);
                        $end_at = date('Y-m-d 23:59:59', time()-16*3600);
                        break;
                    default:
                }
                $where2[]  = ['a.created_at', '>=', strtotime($start_at)];
                $where2[]  = ['a.created_at', '<=', strtotime($end_at)];

                // 广告以及ROI
                $adv_where[]  = ['create_time', '>=', $start_at];
                $adv_where[]  = ['create_time', '<', $end_at];
            }
        }
        if (!empty($filters['tag'])) {
            $where4 = [];
            $keywordtag = '%' . $filters['tag'] . '%';
            $where4[] = ['d.sku', 'like', $keywordtag];
            $where_total_price[] = ['a.financial_status', 'in', [DicOrder::$financial_status_partially_paid, DicOrder::$financial_status_paid, DicOrder::$financial_status_partially_refunded, DicOrder::$financial_status_refunded]];
            $lists = table('order')
                ->field('COUNT(a.id) as count,sum(a.total_price) as total_price,currency,b.tag_source,b.tag_campaign')
                ->alias('a')
                ->join('qu_customerjourney b','b.order_number = a.order_number', 'left')
                ->join('qu_customerjourneysummary c','c.order_id = a.id', 'left')
                ->join('qu_order_quantity d','d.order_id = a.id', 'left')
                ->where('a.site_id','121')
                ->where($where_total_price)
                ->where($where2)
                ->where($where3)
                ->where($where4)
                ->group('b.tag_source,b.tag_campaign')
                ->order('b.tag_source desc,b.tag_campaign desc')
                ->select()
                ->toArray();
        }else{
            $where_total_price[] = ['a.financial_status', 'in', [DicOrder::$financial_status_partially_paid, DicOrder::$financial_status_paid, DicOrder::$financial_status_partially_refunded, DicOrder::$financial_status_refunded]];
            $lists = table('order')
                ->field('COUNT(a.id) as count,sum(a.total_price) as total_price,currency,b.tag_source,b.tag_campaign')
                ->alias('a')
                ->join('qu_customerjourney b','b.order_number = a.order_number', 'left')
                ->join('qu_customerjourneysummary c','c.order_id = a.id', 'left')
                ->where('a.site_id','121')
                ->where($where_total_price)
                ->where($where2)
                ->where($where3)
                ->group('b.tag_source,b.tag_campaign')
                ->order('b.tag_source desc,b.tag_campaign desc')
                ->select()
                ->toArray();
        }
            
        $lists_news = array();
        if(!empty($lists)){
            //整合成三维数组
            $groupedStatistics = [];
            foreach ($lists as $item) {
                // 广告以及ROI
                // $admin_id = $this->tag_change($item['tag_campaign'], 3);
                $advcosts = 0;
                // if(!empty($admin_id)){
                //     $where_admin['admin_id'] = $admin_id;
                //     $advcost = table('advertising')
                //         ->field('sum(cost) as cost')
                //         ->where($adv_where)
                //         ->where('status', 'normal')
                //         ->where($where_admin)
                //         ->find();
                        
                //     if (!empty($advcost['cost'])) {
                //         $advcosts = number_format($advcost['cost'], 3, '.', '');
                //     }
                // }

                $tagSource = $item["tag_source"];
                $totalPrice = $item["total_price"];
                $totalCount = $item["count"];
                if (!empty($tagSource) && is_numeric($totalPrice)) {
                    if (!array_key_exists($tagSource, $groupedStatistics)) {
                        $groupedStatistics[$tagSource] = ["advcost_sum" => 0,"total_price_sum" => 0,"count" => 0, "info" => []];
                    }
                    $groupedStatistics[$tagSource]["total_price_sum"] += $totalPrice;
                    $groupedStatistics[$tagSource]["count"] += $totalCount;
                    $groupedStatistics[$tagSource]["advcost_sum"] += $advcosts;
                    if (!in_array($tagSource, $groupedStatistics[$tagSource]["info"])) {
                        $item['advcost'] = $advcosts;
                        $groupedStatistics[$tagSource]["info"][] = $item;
                    }
                }
            }
            //统计
            foreach ($groupedStatistics as $k => $v) {
                $tag_source = $this->tag_change($k, 1);
                foreach ($v['info'] as $key => $val) {
                    $tag_campaign = $this->tag_change($val['tag_campaign'], 2);

                    //ROI
                    if ($val['advcost'] > 0.001 && $val['total_price'] > 0.001) {
                        $roi = number_format((float)$val['total_price'] / (float)$val['advcost'], 3, '.', '');
                    }else{
                        $roi = 0;
                    }

                    $lists_new = array(
                        'total_price' => number_format((float)$val['total_price']*1, 2, '.', ''),
                        'count' => $val['count'],
                        'currency' => $val['currency'],
                        'tag_source' => $this->tag_change($val['tag_source'], 1),
                        'advcost' => $val['advcost'],
                        'roi' => $roi,
                        'tag_campaign' => $tag_campaign
                    );
                    $lists_news[] = $lists_new;
                }
                if($k == "直接访问" || $k == "other" ){

                }else{
                    $lists_new = array(
                        'total_price' => number_format((float)$v['total_price_sum']*1, 2, '.', ''),
                        'currency' => "",
                        'count' => $v['count'],
                        'roi' => "",
                        'tag_source' => $tag_source,
                        'advcost' => number_format((float)$v['advcost_sum']*1, 2, '.', ''),
                        'tag_campaign' => "统计"
                    );
                    $lists_news[] = $lists_new;
                }
                $this->total_price_sum = number_format((float)$v['total_price_sum'] + $this->total_price_sum, 2, '.', '');
                $this->count = $v['count'] + $this->count;
            }
        }
        return $lists_news;
    }
    

    /**************************************************************************************************************************不计算退款
    * 方法详情
    * @param array params
    */
    public function lists_news_sku($filters)
    {
        $where2 = array();
        if (isset($filters['name'])) {
            $order_ids = trim($filters['name']);
            $order_ids = explode("\n", $order_ids);
            $order_ids_new = array();
            if (!empty($order_ids)) {
                foreach ($order_ids as $k => $vids) {
                    if (!empty($vids)) {
                        $vids = trim($vids);
                        $order_ids_new[] = $vids;
                    }
                }
            }
            if (!empty($order_ids_new)) {
                $where2[] = ['a.id|a.name|a.order_number', 'in', $order_ids_new];
            }
        }
        if (!empty($filters['number'])) {
            $where2[] = array('a.order_number', 'LIKE', '%' . trim($filters['number']) . '%');
        }
        if (!empty($filters['skunum'])) {
            $where2[] = array('a.two_kf_id', '=',trim($filters['skunum']));
        }
        if (!empty($filters['tag_campaign'])) {
            if($filters['tag_campaign'] == "null"){
                $where2[] = array('b.tag_campaign', '=', '');
            }else{
                $where2[] = array('b.tag_campaign', 'LIKE', '%' . trim($filters['tag_campaign']) . '%');
            }
        }
        if (!empty($filters['tag_source'])) {
            if($filters['tag_source'] == "null"){
                $where2[] = array('b.tag_source', '=', '');
            }else{
                $where2[] = array('b.tag_source', 'LIKE', '%' . trim($filters['tag_source']) . '%');
            }
        }  
        $adv_where = [];
        if (!empty($filters['created_at'])) {
            $where2[]  = ['a.created_at', '>=', strtotime($filters['created_at'][0])];
            $where2[]  = ['a.created_at', '<=', strtotime($filters['created_at'][1]) + 3600*24-1];
        }
        if (!empty($filters['status'])) {
            if($filters['status'] == "matched"){
                $where2[] = array('b.status', '=',1);
            }
            if($filters['status'] == "manually_modifying"){
                $where2[] = array('b.status', '=',3);
            }
            if($filters['status'] == "unmatched"){
                $where2[] = array('b.status', "=",null);
                $where2[]  = ['a.created_at', '>=', 1709222400];
            }
            if($filters['status'] == "matching_failed"){
                $where2[] = array('b.status', '=',2);
            }
            if($filters['status'] == "fb"){
                $where2[] = array('b.tag_source', 'LIKE', '%fb%');
            }
            if($filters['status'] == "tt"){
                $where2[] = array('b.tag_source', 'LIKE', '%tt%');
            }
            if($filters['status'] == "ggad"){
                $where2[] = array('b.tag_source', 'LIKE', '%ggad%');
            }
            if($filters['status'] == "email"){
                $where2[] = array('b.tag_source', 'LIKE', '%email%');
            }
            if($filters['status'] == "other"){
                $where2[] = array('b.tag_source', 'LIKE', '%other%');
            }
            if($filters['status'] == "direct_access"){
                $where2[] = array('b.tag_source', 'LIKE', '%直接访问%');
            }
        }
        $where3 = [];
        if (!empty($filters['key_words'])) {
            $keyword = '%' . $filters['key_words'] . '%';
            $where3[] = ['a.landing_site|c.lastVisit_campaign|c.lastVisit_source|c.firstVisit_campaign|c.firstVisit_source|b.tag_source|b.tag_campaign|b.first_source|b.first_campaign|b.last_source|b.last_campaign', 'like', $keyword];
        }
        if(!empty($where3) || !empty($where2)){
            $start_at = date('Y-m-d 00:00:00', time()-16*3600-365*24*3600);
            $end_at = date('Y-m-d 23:59:59', time()-16*3600);

            // $where2[]  = ['a.created_at', '>=', strtotime($start_at)];
            // $where2[]  = ['a.created_at', '<=', strtotime($end_at)];

            // 广告以及ROI
            $adv_where[]  = ['create_time', '>=', $start_at];
            $adv_where[]  = ['create_time', '<', $end_at];
        }else{
            if (isset($filters['day_changes'])) {
                switch ($filters['day_changes']) {
                    case '0':
                        $start_at = date('Y-m-d 00:00:00', time()-16*3600);
                        $end_at = date('Y-m-d 23:59:59', time()-16*3600);
                        break;
                    case '1':
                        $start_at = date('Y-m-d 00:00:00', time()-16*3600-24*3600);
                        $end_at = date('Y-m-d 23:59:59', time()-16*3600-24*3600);
                        break;
                    case '2':
                        $start_at = date('Y-m-d 00:00:00', time()-16*3600-7*24*3600);
                        $end_at = date('Y-m-d 23:59:59', time()-16*3600);
                        break;
                    case '3':
                        $start_at = date('Y-m-d 00:00:00', time()-16*3600-365*24*3600);
                        $end_at = date('Y-m-d 23:59:59', time()-16*3600);
                        break;
                    default:
                }
                $where2[]  = ['a.created_at', '>=', strtotime($start_at)];
                $where2[]  = ['a.created_at', '<=', strtotime($end_at)];

                // 广告以及ROI
                $adv_where[]  = ['create_time', '>=', $start_at];
                $adv_where[]  = ['create_time', '<', $end_at];
            }
        }
        if (!empty($filters['tag'])) {
            $where4 = [];
            $keywordtag = '%' . $filters['tag'] . '%';
            $where4[] = ['d.sku', 'like', $keywordtag];
            $where_total_price[] = ['a.financial_status', 'in', [DicOrder::$financial_status_partially_paid, DicOrder::$financial_status_paid, DicOrder::$financial_status_partially_refunded, DicOrder::$financial_status_refunded]];
            $lists = table('order')
                ->field('COUNT(a.id) as count,sum(a.total_price) as total_price,currency,b.tag_source,b.tag_campaign')
                ->alias('a')
                ->join('qu_customerjourney b','b.order_number = a.order_number', 'left')
                ->join('qu_customerjourneysummary c','c.order_id = a.id', 'left')
                ->join('qu_order_quantity d','d.order_id = a.id', 'left')
                ->where('a.site_id','121')
                ->where($where_total_price)
                ->where($where2)
                ->where($where3)
                ->where($where4)
                ->group('b.tag_source,b.tag_campaign')
                ->order('b.tag_source desc,b.tag_campaign desc')
                ->select()
                ->toArray();
        }else{
            $where_total_price[] = ['a.financial_status', 'in', [DicOrder::$financial_status_partially_paid, DicOrder::$financial_status_paid, DicOrder::$financial_status_partially_refunded, DicOrder::$financial_status_refunded]];
            $lists = table('order')
                ->field('COUNT(a.id) as count,sum(a.total_price) as total_price,currency,b.tag_source,b.tag_campaign')
                ->alias('a')
                ->join('qu_customerjourney b','b.order_number = a.order_number', 'left')
                ->join('qu_customerjourneysummary c','c.order_id = a.id', 'left')
                ->where('a.site_id','121')
                ->where($where_total_price)
                ->where($where2)
                ->where($where3)
                ->group('b.tag_campaign')
                ->order('b.tag_campaign desc')
                ->select()
                ->toArray();
        }
        $lists_news = array();
        if(!empty($lists)){
                //统计
                foreach ($lists as $k => $val) {
                    $tag_campaign = $this->tag_change($val['tag_campaign'], 2);
                        //细分sku
                        if(!empty($where3)){
                            $tag_campaigns = table('order')
                                ->field('a.freight,a.material,a.skuinfo2,b.tag_campaign,total_price')
                                ->alias('a')
                                ->join('qu_customerjourney b','b.order_number = a.order_number', 'left')
                                ->join('qu_customerjourneysummary c','c.order_id = a.id', 'left')
                                ->where('a.site_id','121')
                                ->where('b.tag_campaign',$val['tag_campaign'])
                                ->where($where_total_price)
                                ->where($where2)
                                ->where($where3)
                                ->select()
                                ->toArray();
                        }else{
                            $tag_campaigns = table('order')
                                ->field('a.freight,a.material,a.skuinfo2,b.tag_campaign,total_price')
                                ->alias('a')
                                ->join('qu_customerjourney b','b.order_number = a.order_number', 'left')
                                ->where('a.site_id','121')
                                ->where('b.tag_campaign',$val['tag_campaign'])
                                ->where($where_total_price)
                                ->where($where2)
                                ->select()
                                ->toArray();
                        }
                        //统计
                        
                        if(!empty($tag_campaigns)){
                            foreach ($tag_campaigns as $kc=>$vc) {
                                $vc['tag_campaign'] = $tag_campaign;
                                $this->skuinfo2($vc);
                            }
                        }
                    
                }
                // halt($this->arr_sku);
                if(!empty($this->arr_sku)){
                    foreach ($this->arr_sku as $ka=>$va) {
                        foreach ($va as $kas=>$vas) {
                            if($kas == "总计"){
                                // $lists_new = array(
                                //     'total_price' => $vas['total_price'],
                                //     'count' => $vas['count'],
                                //     'quantity' => $vas['SingleQuantity'],
                                //     'tag_campaign' => $ka,
                                //     'name' => $kas
                                // );
                                // $lists_news[] = $lists_new;
                            }else{
                                if($kas == "标签为空,未分配"){
                                    $lists_new = array(
                                        'total_price' => $vas['total_price'],
                                        'count' => $vas['count'],
                                        'quantity' => $vas['SingleQuantity'],
                                        'tag_campaign' => $ka,
                                        'name' => $kas
                                    );
                                    $lists_news[] = $lists_new;
                                    //统计2
                                    if(isset($this->total_price_percentage) && !empty($this->total_price_percentage)){
                                        $this->total_price_percentage = (float)number_format((float)$this->total_price_percentage + $vas['total_price'], 3, '.', '');
                                    }else{
                                        $this->total_price_percentage = $vas['total_price'];
                                    }
                                }
                                //统计
                                if(isset($this->total_price) && !empty($this->total_price)){
                                    $this->total_price = (float)number_format((float)$this->total_price + $vas['total_price'], 3, '.', '');
                                }else{
                                    $this->total_price = $vas['total_price'];
                                }
                                if(isset($this->freight) && !empty($this->freight)){
                                    $this->freight = (float)number_format((float)$this->freight + $vas['freight'], 3, '.', '');
                                }else{
                                    $this->freight = $vas['freight'];
                                }
                                if(isset($this->material) && !empty($this->material)){
                                    $this->material = (float)number_format((float)$this->material + $vas['material'], 3, '.', '');
                                }else{
                                    $this->material = $vas['material'];
                                }
                                if(isset($this->SingleQuantity) && !empty($this->SingleQuantity)){
                                    $this->SingleQuantity = (float)number_format((float)$this->SingleQuantity + $vas['SingleQuantity'], 3, '.', '');
                                }else{
                                    $this->SingleQuantity = $vas['SingleQuantity'];
                                }
                                if(isset($this->count_sku) && !empty($this->count_sku)){
                                    $this->count_sku = (float)number_format((float)$this->count_sku + $vas['count'], 3, '.', '');
                                }else{
                                    $this->count_sku = $vas['count'];
                                }
                            }
                        }
                    }
                }
            }
            if(!empty($lists_news)){
                foreach ($lists_news as $k=>$v) {
                    $lists_news[$k]['total_price_percentage'] = (float)number_format((float)$v['total_price'] / (float)$this->total_price_percentage * 100, 1, '.', '') . "%";
                }
            }

        if (!empty($filters['created_at'])) {
            $exchange_time = date('Y-m', strtotime($filters['created_at'][0]));
        }else{
            $exchange_time = date('Y-m', time());
        }
        $exchange = table('exchange')
            ->where('currency','USD')
            ->where('date',$exchange_time)
            ->find();
        if(empty($exchange)){
            $exchange['proportion'] = 7.105;
        }
        $this->total_price = (float)number_format((float)$this->total_price, 3, '.', '');
        $this->freight = (float)number_format((float)$this->freight / (float)$exchange['proportion'], 3, '.', '');
        $this->material = (float)number_format((float)$this->material / (float)$exchange['proportion'], 3, '.', '');
        $this->GrossProfit = (float)number_format($this->total_price - $this->freight - $this->material, 3, '.', '');

        // halt($lists_news);
        return $lists_news;
    }

    /**
    * 方法详情
    * @param array params
    */
    public function skuinfo2($item)
    {
        if (!empty($item['skuinfo2'])) {
            //拆分规则
            $pos1 = strpos($item['skuinfo2'], ',');
            if ($pos1 !== false) {
                $sku = explode(',', $item['skuinfo2']);
                if(!empty($sku)){
                    foreach ($sku as $k1=>$v1) {
                        $spus = explode('*', $v1);
                        if(isset($spus[0]) && !empty($spus[0])){
                            if(isset($this->arr_sku[$spus[0]][$item['tag_campaign']]) && !empty($this->arr_sku[$spus[0]][$item['tag_campaign']])){
                                $this->arr_sku[$spus[0]][$item['tag_campaign']]['total_price'] = (float)number_format((float)$this->arr_sku[$spus[0]][$item['tag_campaign']]['total_price'] + $item['total_price'], 3, '.', '');
                                $this->arr_sku[$spus[0]][$item['tag_campaign']]['count'] = $this->arr_sku[$spus[0]][$item['tag_campaign']]['count'] + 1;
                                $this->arr_sku[$spus[0]][$item['tag_campaign']]['SingleQuantity'] = 
                                $this->arr_sku[$spus[0]][$item['tag_campaign']]['SingleQuantity'] + (int)$spus[1];

                                $this->arr_sku[$spus[0]][$item['tag_campaign']]['freight'] = $item['freight'] + $this->arr_sku[$spus[0]][$item['tag_campaign']]['freight'];
                                $this->arr_sku[$spus[0]][$item['tag_campaign']]['material'] = $item['material'] + $this->arr_sku[$spus[0]][$item['tag_campaign']]['material'];

                            }else{
                                $this->arr_sku[$spus[0]][$item['tag_campaign']]['total_price'] = $item['total_price'];
                                $this->arr_sku[$spus[0]][$item['tag_campaign']]['count'] = 1;
                                $this->arr_sku[$spus[0]][$item['tag_campaign']]['SingleQuantity'] = (int)$spus[1];

                                $this->arr_sku[$spus[0]][$item['tag_campaign']]['freight'] = $item['freight'];
                                $this->arr_sku[$spus[0]][$item['tag_campaign']]['material'] = $item['material'];
                            }

                            //总计
                            // if(isset($this->arr_sku[$spus[0]]["总计"]) && !empty($this->arr_sku[$spus[0]]["总计"])){
                            //     $this->arr_sku[$spus[0]]["总计"]['total_price'] = (float)number_format((float)$this->arr_sku[$spus[0]]["总计"]['total_price'] + $item['total_price'], 3, '.', '');
                            //     $this->arr_sku[$spus[0]]["总计"]['count'] = $this->arr_sku[$spus[0]]["总计"]['count'] + 1;
                            //     $this->arr_sku[$spus[0]]["总计"]['SingleQuantity'] = $this->arr_sku[$spus[0]]["总计"]['SingleQuantity'] + (int)$spus[1];
                            //     $this->arr_sku[$spus[0]]["总计"]['freight'] = $item['freight'] + $this->arr_sku[$spus[0]]["总计"]['freight'];
                            //     $this->arr_sku[$spus[0]]["总计"]['material'] = $item['material'] + $this->arr_sku[$spus[0]]["总计"]['material'];
                            // }else{
                            //     //总计
                            //     $this->arr_sku[$spus[0]]["总计"]['total_price'] = $item['total_price'];
                            //     $this->arr_sku[$spus[0]]["总计"]['count'] = 1;
                            //     $this->arr_sku[$spus[0]]["总计"]['SingleQuantity'] = (int)$spus[1];
                            //     $this->arr_sku[$spus[0]]["总计"]['freight'] = $item['freight'];
                            //     $this->arr_sku[$spus[0]]["总计"]['material'] = $item['material'];
                            // }

                            return true;
                        }
                    }
                }
            } else {
                $pos1 = strpos($item['skuinfo2'], '*');
                if ($pos1 !== false) {
                    $spus = explode('*', $item['skuinfo2']);
                    if(isset($spus[0]) && !empty($spus[0])){

                        if(isset($this->arr_sku[$spus[0]][$item['tag_campaign']]) && !empty($this->arr_sku[$spus[0]][$item['tag_campaign']])){
                            $this->arr_sku[$spus[0]][$item['tag_campaign']]['total_price'] = (float)number_format((float)$this->arr_sku[$spus[0]][$item['tag_campaign']]['total_price'] + $item['total_price'], 3, '.', '');
                            $this->arr_sku[$spus[0]][$item['tag_campaign']]['count'] = $this->arr_sku[$spus[0]][$item['tag_campaign']]['count'] + 1;
                            $this->arr_sku[$spus[0]][$item['tag_campaign']]['SingleQuantity'] = $this->arr_sku[$spus[0]][$item['tag_campaign']]['SingleQuantity'] + (int)$spus[1];

                            $this->arr_sku[$spus[0]][$item['tag_campaign']]['freight'] = $item['freight'] + $this->arr_sku[$spus[0]][$item['tag_campaign']]['freight'];
                            $this->arr_sku[$spus[0]][$item['tag_campaign']]['material'] = $item['material'] + $this->arr_sku[$spus[0]][$item['tag_campaign']]['material'];

                        }else{
                            $this->arr_sku[$spus[0]][$item['tag_campaign']]['total_price'] = $item['total_price'];
                            $this->arr_sku[$spus[0]][$item['tag_campaign']]['count'] = 1;
                            $this->arr_sku[$spus[0]][$item['tag_campaign']]['SingleQuantity'] = (int)$spus[1];

                            $this->arr_sku[$spus[0]][$item['tag_campaign']]['freight'] = $item['freight'];
                            $this->arr_sku[$spus[0]][$item['tag_campaign']]['material'] = $item['material'];
                        }

                        //总计
                        if(isset($this->arr_sku[$spus[0]]["总计"]) && !empty($this->arr_sku[$spus[0]]["总计"])){
                            $this->arr_sku[$spus[0]]["总计"]['total_price'] = (float)number_format((float)$this->arr_sku[$spus[0]]["总计"]['total_price'] + $item['total_price'], 3, '.', '');
                            $this->arr_sku[$spus[0]]["总计"]['count'] = $this->arr_sku[$spus[0]]["总计"]['count'] + 1;
                            $this->arr_sku[$spus[0]]["总计"]['SingleQuantity'] = $this->arr_sku[$spus[0]]["总计"]['SingleQuantity'] + (int)$spus[1];
                            $this->arr_sku[$spus[0]]["总计"]['freight'] = $item['freight'] + $this->arr_sku[$spus[0]]["总计"]['freight'];
                            $this->arr_sku[$spus[0]]["总计"]['material'] = $item['material'] + $this->arr_sku[$spus[0]]["总计"]['material'];
                        }else{
                            //总计
                            $this->arr_sku[$spus[0]]["总计"]['total_price'] = $item['total_price'];
                            $this->arr_sku[$spus[0]]["总计"]['count'] = 1;
                            $this->arr_sku[$spus[0]]["总计"]['SingleQuantity'] = (int)$spus[1];
                            $this->arr_sku[$spus[0]]["总计"]['freight'] = $item['freight'];
                            $this->arr_sku[$spus[0]]["总计"]['material'] = $item['material'];
                        }
                        
                        return true;
                    }
                }
            }
        }
        return true;
    }
    
    /**
    * 方法详情
    * @param array params
    */
    public function tag_change($arr,$type)
    {
        $Taskcustomerjourney = new \app\admin\controller\Taskcustomerjourney($this->app);
        $config = $Taskcustomerjourney->config();
        if($type == 1){
            if(!empty($arr)){
                foreach ($config['str2'] as $k=>$v) {
                    if(strtolower($arr) == strtolower($v)){
                        $arr = $config['str1'][$k];
                        break;
                    }
                }
            }else{
                $arr = "渠道为空,未分配";
            }
            return $arr;
        }
        if($type == 2){
            if(!empty($arr)){
                foreach ($config['str3'] as $k=>$v) {
                    if(strtolower($arr) == strtolower($v)){
                        $arr = $config['str4'][$k];
                        break;
                    }
                }
            }else{
                $arr = "标签为空,未分配";
            }
            return $arr;
        }
        if($type == 3){
            if(!empty($arr)){
                foreach ($config['str3'] as $k=>$v) {
                    if(strtolower($arr) == strtolower($v)){
                        $arr = $config['str5'][$k];
                        break;
                    }
                }
            }else{
                $arr = "0";
            }
            return $arr;
        }
    }
    

    /**
     * 上传文件
     */
    public function uploads()
    {
        $file = $this->request->file('file');
        // try {
        $upload = new Upload();
        $res = $upload->upload($file);
        if ($res['url']) {
            // return success('ok', $res['url']);
            $return = $this->import_cost($res['url']);
            return success('ok', $return);
        } else {
            return error($res['msg']);
        }
        // } catch (\Exception $e) {
        //     Log::error("---ajax---upload--error:" . $e);
        // }
        return error('上传文件失败');
    }

    //导入花费
    public function  import_cost($url)
    {
        $url_arr = explode('storage/', $url);
        $data = Excel::import('storage/' . $url_arr[1]);
        unset($data[1]);
        $num_error = 0;
        $num_success = 0;
        foreach ($data as  $value) {
            $customerjourney = table('customerjourney')
                ->where(['order_number' => $value[0]])
                ->find();
            if (!empty($customerjourney)) {
                $arr_insert = [
                    'tag_source' => isset($value[1]) ? $value[1] : "",
                    'tag_campaign' => isset($value[2]) ? $value[2] : "",
                    'status' => 3,
                ];
                table('customerjourney')
                    ->where('id', $customerjourney['id'])
                    ->update($arr_insert);
                //tags
                $msg_data = array();
                if(!empty($value[1])){
                    $msg_data[] = strtolower($value[1]);
                }
                if(!empty($value[2])){
                    $msg_data[] = strtolower($value[2]);
                }
                if(!empty($msg_data)){
                    $tags = implode(',',$msg_data);
                }else{
                    $tags = "";
                }
                $arr_insert2 = [
                    'number' => $value[0],
                    'tags' => $tags,
                    'type' => 0,
                ];
                $customerj_handle = table('customerj_handle')->where('number',$arr_insert2['number'])->find();
                if(!empty($customerj_handle)){
                    // table('customerj_handle')->where('number',$arr_insert2['number'])->update($arr_insert2);
                }else{
                    // table('customerj_handle')->insert($arr_insert2);
                }
                $num_success = $num_success + 1;
            }else{
                $num_error = $num_error + 1;
            }
        }
        $info = "导入完成：成功" . $num_success . "个，失败" . $num_error . "个。";
        return $info;
    }

    //批量完成
    public function batch_confirmation()
    {
        $post = $this->request->post();
        $data = $post['multiple_customerjourney'];
        $num_error = 0;
        $num_success = 0;
        if(!empty($data)){
            foreach ($data as $key=>$value) {
                $arr_update = [
                    'status' => 1,
                ];
                $customerj_today = table('customerj_today')->where('id',$value['id'])->update($arr_update);
                if(!empty($customerj_today)){
                    $num_success = $num_success + 1;
                }else{
                    $num_error = $num_error + 1;
                }
            }
        }
        $info = "成功" . $num_success . "个，失败" . $num_error . "个。";
        return success("操作完成：".$info);
    }

    /**
     * 导出明细
     */
    public function export_achievement_order()
    {
        $filters = $this->request->get('filter', '{}');
        $filters = json_decode($filters, true);
        $lists_news = $this->order_news($filters);

        $header = array();
        $header[] = ['日期', 'created_at'];
        $header[] = ['店铺', 'site_id'];
        $header[] = ['订单号', 'name'];
        $header[] = ['销售额$', 'total_price'];
        $header[] = ['物流￥', 'freight'];
        $header[] = ['采购￥', 'material'];
        $header[] = ['币种', 'currency'];
        $header[] = ['支付渠道', 'gateway'];
        $header[] = ['支付状态', 'financial_status'];
        $header[] = ['收货状态', 'fulfillment_status'];
        $header[] = ['标签', 'tags'];
        $header[] = ['争议状态', 'dispute_status'];
        $header[] = ['争议金额', 'dispute_cost'];
        $header[] = ['备注', 'remarks'];
        $header[] = ['运费是否估算', 'estimate_freight'];
        $header[] = ['采购是否估算', 'estimate_material'];
        $header[] = ['渠道标签', 'tag_source'];
        $header[] = ['运营标签', 'tag_campaign'];
        $header[] = ['first_source', 'first_source'];
        $header[] = ['first_campaign', 'first_campaign'];
        $header[] = ['last_source', 'last_source'];
        $header[] = ['last_campaign', 'last_campaign'];


        // $time1  = date('Y-m-d', strtotime($filters['created_at'][0]));
        // $time2  = date('Y-m-d', strtotime($filters['created_at'][1]) + 3600*24-1);

        $fileName = "orderinfo";
        
        return Excel::exportData($lists_news, $header, $fileName, 'xlsx');
    }

    /**
    * 方法详情
    * @param array params
    */
    public function order_news($filters)
    {
        $where2 = array();
        if (isset($filters['name'])) {
            $order_ids = trim($filters['name']);
            $order_ids = explode("\n", $order_ids);
            $order_ids_new = array();
            if (!empty($order_ids)) {
                foreach ($order_ids as $k => $vids) {
                    if (!empty($vids)) {
                        $vids = trim($vids);
                        $order_ids_new[] = $vids;
                    }
                }
            }
            if (!empty($order_ids_new)) {
                $where2[] = ['a.id|a.name|a.order_number', 'in', $order_ids_new];
            }
        }
        if (!empty($filters['number'])) {
            $where2[] = array('a.order_number', 'LIKE', '%' . trim($filters['number']) . '%');
        }
        if (!empty($filters['skunum'])) {
            $where2[] = array('a.two_kf_id', '=',trim($filters['skunum']));
        }
        if (!empty($filters['tag_campaign'])) {
            if($filters['tag_campaign'] == "null"){
                $where2[] = array('b.tag_campaign', '=', '');
            }else{
                $where2[] = array('b.tag_campaign', 'LIKE', '%' . trim($filters['tag_campaign']) . '%');
            }
        }
        if (!empty($filters['tag_source'])) {
            if($filters['tag_source'] == "null"){
                $where2[] = array('b.tag_source', '=', '');
            }else{
                $where2[] = array('b.tag_source', 'LIKE', '%' . trim($filters['tag_source']) . '%');
            }
        }  
        $adv_where = [];
        if (!empty($filters['created_at'])) {
            $where2[]  = ['a.created_at', '>=', strtotime($filters['created_at'][0])];
            $where2[]  = ['a.created_at', '<=', strtotime($filters['created_at'][1]) + 3600*24-1];
        }
        if (!empty($filters['status'])) {
            if($filters['status'] == "matched"){
                $where2[] = array('b.status', '=',1);
            }
            if($filters['status'] == "manually_modifying"){
                $where2[] = array('b.status', '=',3);
            }
            if($filters['status'] == "unmatched"){
                $where2[] = array('b.status', "=",null);
                $where2[]  = ['a.created_at', '>=', 1709222400];
            }
            if($filters['status'] == "matching_failed"){
                $where2[] = array('b.status', '=',2);
            }
            if($filters['status'] == "fb"){
                $where2[] = array('b.tag_source', 'LIKE', '%fb%');
            }
            if($filters['status'] == "tt"){
                $where2[] = array('b.tag_source', 'LIKE', '%tt%');
            }
            if($filters['status'] == "ggad"){
                $where2[] = array('b.tag_source', 'LIKE', '%ggad%');
            }
            if($filters['status'] == "email"){
                $where2[] = array('b.tag_source', 'LIKE', '%email%');
            }
            if($filters['status'] == "other"){
                $where2[] = array('b.tag_source', 'LIKE', '%other%');
            }
            if($filters['status'] == "direct_access"){
                $where2[] = array('b.tag_source', 'LIKE', '%直接访问%');
            }
        }
        $where3 = [];
        if (!empty($filters['key_words'])) {
            $keyword = '%' . $filters['key_words'] . '%';
            $where3[] = ['a.landing_site|c.lastVisit_campaign|c.lastVisit_source|c.firstVisit_campaign|c.firstVisit_source|b.tag_source|b.tag_campaign|b.first_source|b.first_campaign|b.last_source|b.last_campaign', 'like', $keyword];
        }
        if(!empty($where3) || !empty($where2)){
            $start_at = date('Y-m-d 00:00:00', time()-16*3600-365*24*3600);
            $end_at = date('Y-m-d 23:59:59', time()-16*3600);

            // $where2[]  = ['a.created_at', '>=', strtotime($start_at)];
            // $where2[]  = ['a.created_at', '<=', strtotime($end_at)];

            // 广告以及ROI
            $adv_where[]  = ['create_time', '>=', $start_at];
            $adv_where[]  = ['create_time', '<', $end_at];
        }else{
            if (isset($filters['day_changes'])) {
                switch ($filters['day_changes']) {
                    case '0':
                        $start_at = date('Y-m-d 00:00:00', time()-16*3600);
                        $end_at = date('Y-m-d 23:59:59', time()-16*3600);
                        break;
                    case '1':
                        $start_at = date('Y-m-d 00:00:00', time()-16*3600-24*3600);
                        $end_at = date('Y-m-d 23:59:59', time()-16*3600-24*3600);
                        break;
                    case '2':
                        $start_at = date('Y-m-d 00:00:00', time()-16*3600-7*24*3600);
                        $end_at = date('Y-m-d 23:59:59', time()-16*3600);
                        break;
                    case '3':
                        $start_at = date('Y-m-d 00:00:00', time()-16*3600-365*24*3600);
                        $end_at = date('Y-m-d 23:59:59', time()-16*3600);
                        break;
                    default:
                }
                $where2[]  = ['a.created_at', '>=', strtotime($start_at)];
                $where2[]  = ['a.created_at', '<=', strtotime($end_at)];

                // 广告以及ROI
                $adv_where[]  = ['create_time', '>=', $start_at];
                $adv_where[]  = ['create_time', '<', $end_at];
            }
        }
        if (!empty($filters['tag'])) {
            $where4 = [];
            $keywordtag = '%' . $filters['tag'] . '%';
            $where4[] = ['d.sku', 'like', $keywordtag];

            $where_total_price[] = ['a.financial_status', 'in', [DicOrder::$financial_status_partially_paid, DicOrder::$financial_status_paid, DicOrder::$financial_status_partially_refunded, DicOrder::$financial_status_refunded]];
            $lists = table('order')
                ->field('
                    a.order_number,
                    a.created_at,
                    a.site_id,
                    a.name,
                    a.total_price,
                    a.freight,
                    a.material,
                    a.currency,
                    a.gateway,
                    a.financial_status,
                    a.fulfillment_status,
                    a.tags,
                    a.dispute_status,
                    a.dispute_cost,
                    a.istransfer as estimate_freight,
                    a.ismaterial as estimate_material,
                    b.tag_source,
                    b.tag_campaign,
                    b.first_source,
                    b.first_campaign,
                    b.last_source,
                    b.last_campaign
                ')
                ->alias('a')
                ->join('qu_customerjourney b','b.order_number = a.order_number', 'left')
                ->join('qu_customerjourneysummary c','c.order_id = a.id', 'left')
                ->join('qu_order_quantity d','d.order_id = a.id', 'left')
                ->where('a.site_id','121')
                ->where($where_total_price)
                ->where($where2)
                ->where($where3)
                ->where($where4)
                ->group('a.order_number')
                ->order('a.created_at')
                // ->limit($limit1, $limit2)
                ->select()
                ->toArray();
        }else{
            $where_total_price[] = ['a.financial_status', 'in', [DicOrder::$financial_status_partially_paid, DicOrder::$financial_status_paid, DicOrder::$financial_status_partially_refunded, DicOrder::$financial_status_refunded]];
            $lists = table('order')
                ->field('
                    a.order_number,
                    a.created_at,
                    a.site_id,
                    a.name,
                    a.total_price,
                    a.freight,
                    a.material,
                    a.currency,
                    a.gateway,
                    a.financial_status,
                    a.fulfillment_status,
                    a.tags,
                    a.dispute_status,
                    a.dispute_cost,
                    a.istransfer as estimate_freight,
                    a.ismaterial as estimate_material,
                    b.tag_source,
                    b.tag_campaign,
                    b.first_source,
                    b.first_campaign,
                    b.last_source,
                    b.last_campaign
                ')
                ->alias('a')
                ->join('qu_customerjourney b','b.order_number = a.order_number', 'left')
                ->join('qu_customerjourneysummary c','c.order_id = a.id', 'left')
                ->where('a.site_id','121')
                ->where($where_total_price)
                ->where($where2)
                ->where($where3)
                ->group('a.order_number')
                ->order('a.created_at')
                // ->limit($limit1, $limit2)
                ->select()
                ->toArray();
        }
            
        if(!empty($lists)){
            foreach ($lists as $k=>$v) {
                $lists[$k]['created_at'] = date('Y-m-d H:i:s', $v['created_at']);
                $lists[$k]['site_id'] = "SheCurve";
                $lists[$k]['remarks'] = "";
                switch ($v['dispute_status']) {
                    case '0':
                        $lists[$k]['dispute_status'] = '正常';
                        break;
                    case '1':
                        $lists[$k]['dispute_status'] = '有争议';
                        $lists[$k]['remarks'] = '争议订单不算销售额';
                        break;
                    default:
                        $lists[$k]['dispute_status'] = '未知';
                }
                if(!empty($lists[$k]['estimate_freight'])){
                    switch ($lists[$k]['estimate_freight']) {
                        case '0':
                            $lists[$k]['estimate_freight'] = '等待估算';
                            break;
                        case '1':
                            $lists[$k]['estimate_freight'] = '真实运费';
                            break;
                        case '2':
                            $lists[$k]['estimate_freight'] = '估算';
                            break;
                        default:
                            $lists[$k]['estimate_freight'] = '未知';
                    }
                }else{
                    $lists[$k]['estimate_freight'] = '等待估算';
                }
                if(!empty($lists[$k]['estimate_material'])){
                    switch ($lists[$k]['estimate_material']) {
                        case '0':
                            $lists[$k]['estimate_material'] = '等待估算';
                            break;
                        case '1':
                            $lists[$k]['estimate_material'] = '真实采购费';
                            break;
                        case '2':
                            $lists[$k]['estimate_material'] = '估算';
                            break;
                        default:
                            $lists[$k]['estimate_material'] = '未知';
                    }
                }else{
                    $lists[$k]['estimate_material'] = '等待估算';
                }
                $lists[$k]['tag_campaign'] = $this->tag_change($v['tag_campaign'], 2);
                $lists[$k]['tag_source'] = $this->tag_change($v['tag_source'], 1);
            }
        }
        return $lists;
    }

}
